package Database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Model.SalesOrder;

public class DBSalesOrder implements IFDBSalesOrder {
    private Connection con;

    /** Creates a new instance of SalesOrder */
    public DBSalesOrder() {
	con = DbConnection.getInstance().getDBcon();
    }

    public ArrayList<SalesOrder> getAllSalesOrders() {
	return miscWhere("");
    }

    public SalesOrder findSalesOrder(int orderID) {
	String wClause = "  orderID = " + orderID + "";
	return singleWhere(wClause);
    }

    public int insertSalesOrder(SalesOrder salesOrder) throws Exception { // call
									  // to
									  // get
	// the next ssn
	// number

	int rc = -1;

	String query = "INSERT INTO salesOrder(sDate, amount, deliveryStatus, deliveryDate, orderID, customerID)  VALUES('" + salesOrder.getDate() + "'," + salesOrder.getAmount() + ",'" + salesOrder.getDeliveryStatus() + "','" + salesOrder.getDeliveryDate() + "'," + salesOrder.getOrderID() + "," + salesOrder.getCustomer() + ");";
	System.out.println(salesOrder.getDate());
	System.out.println("insert : " + query);
	try { // insert new employee + dependent
	    Statement stmt = con.createStatement();
	    stmt.setQueryTimeout(5);
	    rc = stmt.executeUpdate(query);
	    stmt.close();
	}// end try
	catch (SQLException ex) {
	    System.out.println("SalesOrder ikke oprettet");
	    throw new Exception("SalesOrder is not inserted correct");
	}
	return (rc);
    }

    public int deleteSalesOrder(int id) {
	int rc = -1;
	PreparedStatement pstmt = null;
	String delete = "delete from SalesOrder where orderID = ?";
	System.out.println(delete);
	try {
	    pstmt = con.prepareStatement(delete);
	    pstmt.setInt(1, id);
	    rc = pstmt.executeUpdate();
	} catch (SQLException sqlE) {
	    System.out.println("SQL Error");
	    System.out.println(sqlE.getMessage());
	} catch (Exception e) {
	    e.getMessage();

	}
	return rc;
    }

    // find one
    private SalesOrder buildSalesOrder(ResultSet results) {
	System.out.println(" customer");
	SalesOrder salesOrder = new SalesOrder();
	try { // the columns from the table emplayee are used
	    salesOrder.setOrderID(results.getInt("orderID"));
	    salesOrder.setDate(results.getString("sDate"));
	    salesOrder.setAmount(results.getInt("amount"));
	    salesOrder.setDeliveryStatus(results.getString("deliveryStatus"));
	    salesOrder.setDeliveryDate(results.getString("deliveryDate"));
	    salesOrder.setCustomer(results.getInt("customerID"));
	    
	} catch (Exception e) {
	    System.out.println("error in building the salesOrder object");
	}
	return salesOrder;
    }

    public int updateSalesOrder(SalesOrder order) {
	SalesOrder salesOrder = order;
	int rc = -1;
	String query = "UPDATE salesOrder SET " + "sDate =" + salesOrder.getDate() + ", " + " amount= " + salesOrder.getAmount() + ", " + "deliveryStatus='" + salesOrder.getDeliveryStatus() + "',  " + "deliveryDate='" + salesOrder.getDeliveryDate() + "', " + " customerID= " + salesOrder.getCustomer() + "";
	System.out.println("Update query:" + query);
	try { // update employee
	    Statement stmt = con.createStatement();
	    stmt.setQueryTimeout(5);
	    rc = stmt.executeUpdate(query);

	    stmt.close();
	}// slut try
	catch (Exception ex) {
	    System.out.println("Update exception in customer db: " + ex);
	}
	return (rc);
    }

    private String buildQuery(String wClause) {
	String query = "SELECT sDate, amount, deliveryStatus, deliveryDate, orderID, customerID FROM salesOrder";

	if (wClause.length() > 0)
	    query = query + " WHERE " + wClause;

	return query;
    }

    private SalesOrder singleWhere(String wClause) {
	ResultSet results;
	SalesOrder salesOrder = new SalesOrder();

	String query = buildQuery(wClause);
	System.out.println(query);
	try { // read the salesOrder from the database
	    Statement stmt = con.createStatement();
	    stmt.setQueryTimeout(5);
	    results = stmt.executeQuery(query);

	    if (results.next()) {
		salesOrder = buildSalesOrder(results);
		// assocaition is to be build
		stmt.close();

		// The supervisor and department is to be build as well
		int id = salesOrder.getCustomer();

		salesOrder.setCustomer(id);

		System.out.println("Supervisor is seleceted");
		// here the department has to be selected as well

	    }
	}// end try
	catch (Exception e) {
	    System.out.println("Query exception: " + e);
	}
	return salesOrder;
    }

    private ArrayList<SalesOrder> miscWhere(String wClause) {
	ResultSet results;
	ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();

	String query = buildQuery(wClause);

	try {
	    Statement stmt = con.createStatement();
	    stmt.setQueryTimeout(5);
	    results = stmt.executeQuery(query);

	    while (results.next()) {
		SalesOrder salesOrder = new SalesOrder();
		salesOrder = buildSalesOrder(results);
		list.add(salesOrder);
	    }// end while
	    stmt.close();
	    for (SalesOrder salesOrder : list) {
		int id = salesOrder.getCustomer();
		salesOrder.setCustomer(id);
		System.out.println("Customer is seleceted");
		// here the department has to be selected as well
	    }

	}// slut try
	catch (Exception e) {
	    System.out.println("Query exception - select: " + e);
	    e.printStackTrace();
	}
	return list;
    }
}
